# -*- coding: utf-8 -*-
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
import time
import xlrd
#import xlwt


class Poi(object):
    """
    2020宏观查询1231-区县
    数据的导入

    1、读取文件数据
    2、写入库表
    """


    #多行插入
    def insert_many(self, connect, cursor, insert_data):
        # 获取数据，写入数据库 %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        insert_sql = """
        insert into ori_poi_info_gd_20210106(
            city_name, poi_type, poi_name, lnglat, gd_type, province, full_name, county_name, address
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        connect.ping(reconnect = True)

        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True


if __name__ == "__main__":

    # 获取数据库连接
    mysql_con = MysqlCon()
    connect_odsdb, cursor_odsdb = mysql_con.con_odsdb()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    poi = Poi()

    # 读取原文件
    workbook = xlrd.open_workbook(r'D:\\usr\\EJU\\数据部门\\大数据系统\\商业客户数据部\\数据迁移\\待入库文件20210111\\POI信息.xlsx')
    table = workbook.sheet_by_index(0)
    maxRow = table.nrows
    print("总记录数", maxRow)

    insertDataList = []
    for rowNum in range(maxRow):
        #跳过第一行表头
        if rowNum == 0 :
            #跳过第一行表头
            header = table.row_values(rowNum)
            print(header)
            continue
        rowVale = table.row_values(rowNum)
        #print(rowVale)
        insertDataList.append(rowVale)
        #print(len(rowVale))
        if rowNum > 0 and (rowNum % 1000 == 0 or rowNum == maxRow-1):
            print(rowNum)
            # 批量写入1000条数据
            poi.insert_many(connect_odsdb, cursor_odsdb, insertDataList)
            insertDataList.clear()
            #break

    connect_odsdb.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
